package com.statistics.yfdata.dao;

import com.statistics.bean.Water;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.List;

/**
 * @author MeowParse
 * @date 2017/9/1.
 */
@Repository
public class DataWaterRepository {

	@Autowired
	@Qualifier("primaryJdbcTemplate")
	private JdbcTemplate primaryJdbcTemplate;

	@Autowired
	@Qualifier("secondaryJdbcTemplate")
	private JdbcTemplate secondaryJdbcTemplate;

	private SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");


	public List<Water> getTotalWater(String mac, long start, long end, String month) {

		String tableName = "history_water_" + month;
		String sql = String.format("select max.water_flow - min.water_flow as total_water,max.offset from " +
						"(select max(water_flow) as water_flow,offset from %s where mac =? and create_time >= ? and create_time < ? group by offset) as max ," +
						"(select min(water_flow) as water_flow from %s where mac =? and create_time >= ? and create_time < ? group by offset) as min group by max.offset",
				tableName, tableName);

		RowMapper<Water> rm = BeanPropertyRowMapper.newInstance(Water.class);

		List<Water> waters = secondaryJdbcTemplate.query(sql, new Object[]{mac, start, end, mac, start, end}, rm);
		System.out.println(sql);

		if (waters != null && waters.size() > 0) {
			System.out.println(waters.size());
			System.out.println(mac + "," + start + "," + end);
			return waters;
		}

		return null;
	}

	public List<Water> getSubtractionByWeek(String mac, Long start, Long end) {

		String start_time = simpleDateFormat.format(start);
		String end_time = simpleDateFormat.format(end);

		String sql = "select sum(total_water) as total_water,offset " +
				"from tbl_water_statistics_day where mac = ? and statistics_time >= ? " +
				"and statistics_time < ? GROUP BY `offset`";
		RowMapper<Water> rm = BeanPropertyRowMapper.newInstance(Water.class);
		List<Water> waters = primaryJdbcTemplate.query(sql, new Object[]{mac, start_time, end_time}, rm);

		if (waters != null && waters.size() > 0) {
			return waters;
		}
		return null;

	}
}
